package com.example.employeemanagement.mapper;

import com.example.employeemanagement.dto.DepartmentSalaryDto;
import com.example.employeemanagement.dto.PositionSalaryDto;
import com.example.employeemanagement.dto.SalaryRangeDto;
import com.example.employeemanagement.dto.dashboard.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 数据大屏Mapper接口
 */
@Mapper
public interface DashboardMapper {

    /**
     * 获取员工总数
     */
    @Select("SELECT COUNT(*) FROM employee")
    int getEmployeeCount();

    /**
     * 获取本月新增员工数
     */
    @Select("SELECT COUNT(*) FROM employee WHERE DATE_FORMAT(hire_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')")
    int getNewEmployeeCount();

    /**
     * 获取员工部门分布
     */
    @Select("SELECT d.department_id as departmentId, d.department_name as departmentName, COUNT(e.employee_id) as employeeCount, "
            + "ROUND(COUNT(e.employee_id) * 100.0 / (SELECT COUNT(*) FROM employee), 2) as percentage "
            + "FROM employee e "
            + "JOIN department d ON e.department_id = d.department_id "
            + "GROUP BY d.department_id, d.department_name")
    List<DepartmentDistributionDto> getDepartmentDistribution();

    /**
     * 获取员工职位分布
     */
    @Select("SELECT p.position_id as positionId, p.position_name as positionName, COUNT(e.employee_id) as employeeCount, "
            + "ROUND(COUNT(e.employee_id) * 100.0 / (SELECT COUNT(*) FROM employee), 2) as percentage "
            + "FROM employee e "
            + "JOIN position p ON e.position_id = p.position_id "
            + "GROUP BY p.position_id, p.position_name")
    List<PositionDistributionDto> getPositionDistribution();

    /**
     * 获取考勤状态统计
     */
    @Select("SELECT status, COUNT(*) as count, "
            + "ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM attendance), 2) as percentage "
            + "FROM attendance "
            + "GROUP BY status")
    List<AttendanceStatusDto> getAttendanceStatus();

    /**
     * 获取每日考勤趋势
     */
    @Select("SELECT DATE_FORMAT(attendanceDate, '%Y-%m-%d') as date, "
            + "COUNT(DISTINCT employeeId) as attendanceCount, "
            + "(SELECT COUNT(*) FROM employee) as totalCount, "
            + "ROUND(COUNT(DISTINCT employeeId) * 100.0 / (SELECT COUNT(*) FROM employee), 2) as percentage "
            + "FROM attendance "
            + "WHERE attendanceDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) "
            + "GROUP BY DATE_FORMAT(attendanceDate, '%Y-%m-%d') "
            + "ORDER BY date")
    List<AttendanceTrendDto> getAttendanceTrend();
    
    /**
     * 根据日期范围获取每日考勤趋势
     * @param startDate 开始日期
     * @param endDate 结束日期
     */
    @Select("SELECT DATE_FORMAT(attendanceDate, '%Y-%m-%d') as date, "
            + "COUNT(DISTINCT employeeId) as attendanceCount, "
            + "(SELECT COUNT(*) FROM employee) as totalCount, "
            + "ROUND(COUNT(DISTINCT employeeId) * 100.0 / (SELECT COUNT(*) FROM employee), 2) as percentage "
            + "FROM attendance "
            + "WHERE attendanceDate >= #{startDate} AND attendanceDate <= #{endDate} "
            + "GROUP BY DATE_FORMAT(attendanceDate, '%Y-%m-%d') "
            + "ORDER BY date")
    List<AttendanceTrendDto> getAttendanceTrendByDateRange(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 获取部门考勤对比
     */
    @Select("SELECT d.department_name as departmentName, "
            + "COUNT(DISTINCT a.employeeId) as attendanceCount, "
            + "COUNT(DISTINCT e.employee_id) as totalCount, "
            + "ROUND(COUNT(DISTINCT a.employeeId) * 100.0 / COUNT(DISTINCT e.employee_id), 2) as percentage "
            + "FROM department d "
            + "LEFT JOIN employee e ON d.department_id = e.department_id "
            + "LEFT JOIN attendance a ON e.employee_id = a.employeeId AND DATE(a.attendanceDate) = CURDATE() "
            + "GROUP BY d.department_name")
    List<AttendanceTrendDto> getDepartmentAttendance();

    /**
     * 获取部门平均绩效
     */
    @Select("SELECT d.department_name as departmentName, AVG(p.score) as avgScore "
            + "FROM department d "
            + "JOIN employee e ON d.department_id = e.department_id "
            + "JOIN performance p ON e.employee_id = p.employee_id "
            + "WHERE p.evaluation_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) "
            + "GROUP BY d.department_name")
    List<DepartmentPerformanceDto> getDepartmentPerformance();

    /**
     * 获取薪资区间分布
     */
    @Select("SELECT "
            + "CASE "
            + "  WHEN salary < 5000 THEN '0-5k' "
            + "  WHEN salary >= 5000 AND salary < 10000 THEN '5k-10k' "
            + "  WHEN salary >= 10000 AND salary < 15000 THEN '10k-15k' "
            + "  WHEN salary >= 15000 AND salary < 20000 THEN '15k-20k' "
            + "  ELSE '20k+' "
            + "END as `range`, "
            + "COUNT(*) as count, "
            + "ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employee), 2) as percentage "
            + "FROM employee "
            + "GROUP BY `range` "
            + "ORDER BY CASE "
            + "  WHEN `range` = '0-5k' THEN 1 "
            + "  WHEN `range` = '5k-10k' THEN 2 "
            + "  WHEN `range` = '10k-15k' THEN 3 "
            + "  WHEN `range` = '15k-20k' THEN 4 "
            + "  ELSE 5 "
            + "END")
    List<SalaryRangeDto> getSalaryDistribution();

    /**
     * 获取部门平均薪资
     */
    @Select("SELECT d.department_name as departmentName, AVG(e.salary) as avgSalary "
            + "FROM department d "
            + "JOIN employee e ON d.department_id = e.department_id "
            + "GROUP BY d.department_name")
    List<DepartmentSalaryDto> getDepartmentSalary();

    /**
     * 获取职位平均薪资
     */
    @Select("SELECT p.position_name as positionName, AVG(e.salary) as avgSalary "
            + "FROM position p "
            + "JOIN employee e ON p.position_id = e.position_id "
            + "GROUP BY p.position_name")
    List<PositionSalaryDto> getPositionSalary();
}